package cn.edu.nju.software.sqlitemanage;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import cn.edu.nju.software.beans.AccountBean;

public class ManageZhangHao {
	// add an account for own institute
	public static boolean addAccount(ArrayList<AccountBean> newAccounts)
			throws SQLException, ClassNotFoundException {

		Connection conn = ManageConnection.getConnection();
		boolean updateOk = false;

		try {
			String command = "INSERT INTO zhanghao VALUES (?,?,?)";
			PreparedStatement prepareState = conn.prepareStatement(command);

			for (AccountBean account : newAccounts) {
				String aname = account.getName();
				String apassword = account.getPassword();
				String asid = account.getSid();

				prepareState.setString(1, aname);
				prepareState.setString(2, apassword);
				prepareState.setString(3, asid);

				if (!ManageZhangHao.checkExistence(aname)) {
					prepareState.executeUpdate();
				}
			}
			updateOk = true;
		} catch (Exception exc) {
			updateOk = false;
		} finally {
			conn.close();
		}

		return updateOk;
	}

	// delete an account of own institute
	public static boolean deleteAccount(ArrayList<String> sids)
			throws SQLException, ClassNotFoundException {

		Connection conn = ManageConnection.getConnection();
		boolean updateOk = false;

		try {
			String command = "DELETE FROM zhanghao WHERE xueshengid = ?";
			PreparedStatement prepareState = conn.prepareStatement(command);

			for (String sid : sids) {
				prepareState.setString(1, sid);

				prepareState.executeUpdate();
			}
			updateOk = true;
		} catch (Exception exc) {
			updateOk = false;
		} finally {
			conn.close();
		}

		return updateOk;
	}

	// check if the password is right
	public static boolean checkLogin(String aName, String aPassword)
			throws SQLException, ClassNotFoundException {

		Connection conn = ManageConnection.getConnection();
		boolean isRight = false;

		try {
			String command = "SELECT * FROM zhanghao WHERE callwhat = ?";
			PreparedStatement prepareState = conn.prepareStatement(command);
			prepareState.setString(1, aName);

			ResultSet rs = prepareState.executeQuery();
			while (rs.next()) {
				String realPassword = rs.getString(2);
				if (realPassword.equals(aPassword)) {
					isRight = true;
				}
			}
		} catch (Exception exc) {
			isRight = false;
		} finally {
			conn.close();
		}

		return isRight;
	}

	// get student id by given account name
	public static String getSId(String aName) throws SQLException,
			ClassNotFoundException {

		Connection conn = ManageConnection.getConnection();
		String asid = "";

		try {
			String command = "SELECT * FROM zhanghao WHERE callwhat = ?";
			PreparedStatement prepareState = conn.prepareStatement(command);
			prepareState.setString(1, aName);

			ResultSet rs = prepareState.executeQuery();
			while (rs.next()) {
				asid = rs.getString(3);
			}
		} catch (Exception exc) {

		} finally {
			conn.close();
		}

		return asid;
	}

	// change the password
	public static boolean changePassword(String aName, String oldPassword,
			String newPassword) throws SQLException, ClassNotFoundException {

		Connection conn = ManageConnection.getConnection();
		boolean succeed = false;

		try {
			String command1 = "SELECT * FROM zhanghao WHERE callwhat = ?";
			String command2 = "UPDATE zhanghao SET password = ? WHERE callwhat = ?";
			PreparedStatement prepareState = conn.prepareStatement(command1);
			prepareState.setString(1, aName);

			ResultSet rs = prepareState.executeQuery();
			while (rs.next()) {
				String realPassword = rs.getString(2);
				if (realPassword.equals(oldPassword)) {
					prepareState = conn.prepareStatement(command2);
					prepareState.setString(1, newPassword);
					prepareState.setString(2, aName);
					prepareState.executeUpdate();

					succeed = true;
				}
			}
		} catch (Exception exc) {

		} finally {
			conn.close();
		}

		return succeed;
	}

	public static boolean checkExistence(String name)
			throws ClassNotFoundException, SQLException {

		Connection conn = ManageConnection.getConnection();
		boolean exist = false;

		try {
			String command = "SELECT * FROM zhanghao WHERE callwhat = ?";
			PreparedStatement pre = conn.prepareStatement(command);
			pre.setString(1, name);
			ResultSet rs = pre.executeQuery();
			while (rs.next()) {
				exist = true;
			}
		} catch (Exception exc) {
			exist = false;
		} finally {
			conn.close();
		}

		return exist;
	}
}
